package cn.getech.data.development.utils;

import cn.getech.data.development.entity.TableInfo;
import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;

public class MysqlTableUtil {

    private Logger log = LoggerFactory.getLogger(MysqlTableUtil.class);

    private Connection con;

    private static DruidUtil druidUtil = DruidUtil.getInstance();


    public MysqlTableUtil(String serverUrl, String userName, String password) {
        try {
//            Class.forName("com.mysql.jdbc.Driver");
//            con = DriverManager.getConnection(serverUrl, userName, password);
            con = druidUtil.getMysqlConnection(serverUrl, userName, password);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    public MysqlTableUtil(String serverUrl, String dbName, String userName, String password) {
        try {
//            Class.forName("com.mysql.jdbc.Driver");//加载驱动
//            con = DriverManager.getConnection(serverUrl + dbName, userName, password);
            con = druidUtil.getMysqlConnection(serverUrl + dbName, userName, password);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }


//    public Long dataPreviewRowNumByCount(TableInfo tableInfo) {
//        Long totalCount=0L;
//        try{
//            ResultSet resultNum = getResultSet(String.format("select count(1) as count from %s", tableInfo.getTableName()));
//            while (resultNum.next()) {
//                totalCount += resultNum.getLong("count");
//            }
//        } catch(SQLException e) {
//            log.error(e.getMessage());
//        }
//        close();
//        return totalCount;
//    }

    /**
     * 根据查元数据获取表数据条数
     * @param tableInfo
     * @return
     */
    public Long dataPreviewRowNum(TableInfo tableInfo) {
        Long totalCount=0L;
        try{
            ResultSet resultNum = getResultSet(String.format("select p.PARAM_VALUE  as count\n" +
                    "from TBLS t \n" +
                    "inner join DBS d on d.DB_ID = t.DB_ID \n" +
                    "inner join TABLE_PARAMS p on t.TBL_ID = p.TBL_ID \n" +
                    "where d.`NAME` = '%s' and t.`TBL_NAME` = '%s' and PARAM_KEY = 'numRows'", tableInfo.getDbName(),tableInfo.getTableName()));
            while (resultNum.next()) {
                totalCount += resultNum.getLong("count");
            }
        } catch(SQLException e) {
            log.error(e.getMessage());
        } finally {
            close();
        }
        return totalCount;
    }



    /**
     * 根据查元数据获取分区表数据条数
     * @param tableInfo
     * @return
     */
    public Long dataPreviewRowNumByPartition(TableInfo tableInfo) {
        Long totalCount=0L;
        try{
            String countSql = String.format("select sum(a.PARAM_VALUE) as count\n" +
                    "from TBLS t \n" +
                    "left join DBS d\n" +
                    "on t.DB_ID = d.DB_ID\n" +
                    "left join PARTITIONS p\n" +
                    "on t.TBL_ID = p.TBL_ID \n" +
                    "left join PARTITION_PARAMS a\n" +
                    "on p.PART_ID=a.PART_ID\n" +
                    "where t.TBL_NAME='%s' and d.NAME='%s' and a.PARAM_KEY='numRows'",tableInfo.getTableName(),tableInfo.getDbName());
            ResultSet resultNum = getResultSet(countSql);
            while (resultNum.next()) {
                totalCount += resultNum.getLong("count");
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
        } finally {
            close();
        }
        return totalCount;
    }

    public int dataPreviewRowNumByPartitionName(String dbName, String tableName, String partitionName) {
        int totalCount = 0;
        try {
            String countSql = String.format("select sum(a.PARAM_VALUE) as count\n" +
                            "from TBLS t \n" +
                            "    left join DBS d on t.DB_ID = d.DB_ID\n" +
                            "    left join PARTITIONS p on t.TBL_ID = p.TBL_ID \n" +
                            "    left join PARTITION_PARAMS a on p.PART_ID=a.PART_ID\n" +
                            "where t.TBL_NAME='%s' and d.NAME='%s' and a.PARAM_KEY='numRows' and p.PART_NAME='%s'",
                    tableName, dbName, partitionName);
            ResultSet resultNum = getResultSet(countSql);
            while (resultNum.next()) {
                totalCount += resultNum.getInt("count");
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
        } finally {
            close();
        }
        return totalCount;
    }




    /**
     * 获得所有分区（包括联合分区）
     * @param tableInfo 表信息
     * @param day 查询多少天前的分区 null 默认查询所有分区
     * @return
     */
    public List<String> showPartitionAndValue(TableInfo tableInfo,String day) {
        List<String> resultList = new ArrayList<>();
        try{
            String countSql = String.format("select PART_NAME as par from " +
                    "(select TBL_ID from TBLS t inner join DBS d on d.DB_ID = t.DB_ID  " +
                    "where d.`NAME` = '%s' and  t.TBL_NAME ='%s' ) t1  inner join `PARTITIONS` p \n" +
                    "on p.TBL_ID = t1.TBL_ID ", tableInfo.getDbName(), tableInfo.getTableName());
            if (StringUtils.isNotEmpty(day)) {
                countSql = countSql + " WHERE p.CREATE_TIME > UNIX_TIMESTAMP(DATE_SUB(now(),INTERVAL  %s  DAY))";
                countSql = String.format(countSql, day);
            }
            ResultSet result = getResultSet(countSql);
            while (result.next()) {
                List<String> partitionsList = new ArrayList<>();
                partitionsList.add(result.getString("par").replace("=", "='") + "'");
                buildPartition(partitionsList, resultList);
            }
            log.debug("表" + tableInfo.getTableName() + "的分区有:" + resultList.toString());
        } catch (SQLException e) {
            log.error(e.getMessage());
        } finally {
            close();
        }
        return resultList;
    }

    /**
     * 获取所有独立的分区值（拆分联合分区）
     *
     * @param tableInfo
     * @return
     */
    public List<String> showAllAlonePartitionAndValue(TableInfo tableInfo) {
        Set<String> resultList = new HashSet<>();
        try {
            List<String> partitionList = showPartitionAndValue(tableInfo,null);
            if (CollectionUtil.isNotEmpty(partitionList)) {
                for (String partition : partitionList) {
                    String[] pars = partition.split(",");
                    if (pars != null) {
                        for (String par : pars) {
                            resultList.add(par);
                        }
                    }
                }
            }
            log.debug("表" + tableInfo.getTableName() + "的分区有:" + resultList.toString());
        } catch (Exception e) {
            log.error(e.getMessage());
        }
        return new ArrayList<>(resultList);
    }

    public void buildPartition(List<String> partitons, List<String> resultList) {
        List<String> re = new ArrayList<>();
        for (String partition : partitons) {
            if (partition.contains("/")) {
                String[] pars = partition.split("/");
                for (String par : pars) {
                    if (!par.substring(par.length() - 1).equals("'")) {
                        par += "'";
                    }
                    re.add(par);
                }
            } else {
                re.add(partition);
            }
            log.debug("分区信息：---" + StringUtils.join(re, ","));
            resultList.add(StringUtils.join(re, ","));
        }
    }

    /**
     * 获取指定表的所有分区值（不分离联合分区值）
     *
     * @return
     */
    public List<String> getAllPartition(String dbName, String tableName,String day) {
        List<String> resultList = new ArrayList<>();
        try {
            if (StringUtils.isEmpty(day)) {
                day = "1";
            }
            String countSql = String.format("select PART_NAME as par from " +
                    "(select TBL_ID from TBLS t inner join DBS d on d.DB_ID = t.DB_ID  where d.`NAME` = '%s' " +
                    "and  t.TBL_NAME ='%s' ) t1  inner join `PARTITIONS` p on p.TBL_ID = t1.TBL_ID  " +
                            " WHERE p.CREATE_TIME > UNIX_TIMESTAMP(DATE_SUB(now(),INTERVAL  %s  DAY))",
                    dbName, tableName, day);
            log.info("sql:\n{}",countSql);
            ResultSet result = getResultSet(countSql);
            while (result != null && result.next()) {
                resultList.add(result.getString("par"));
            }
            log.debug("表" + tableName + "的分区有:" + String.join(",", resultList));
        } catch (SQLException e) {
            log.error(e.getMessage());
        } finally {
            close();
        }
        return resultList;
    }


    public List tableInfoDataPreview(TableInfo tableInfo, List<String> columnList, Integer page, Integer limit) {
        List<JSONObject> data = new ArrayList<>();
        Integer start = (page - 1) * limit;

        if (CollectionUtil.isEmpty(columnList)) {
            return data;
        }
        try {
            String sql = "select ";
            for (String columns : columnList) {
                sql = sql + columns + " ,";
            }
            sql = sql.substring(0, sql.length() - 1);
            sql = sql + " from  %s.%s limit %d, %d";
            ResultSet result = getResultSet(String.format(sql,tableInfo.getDbName(),tableInfo.getTableName(), start, limit));
            ResultSetMetaData tsmd=result.getMetaData();
//            for (int i = 1; i < tsmd.getColumnCount()+1; i++) {
//                columnList.add(tsmd.getColumnName(i).replaceAll(String.format("%s.",tableInfo.getTableName()),""));
//            }
            while(result.next()){
                JSONObject object=new JSONObject();
                for (int i = 1; i < tsmd.getColumnCount()+1; i++) {
                    String titleName = tsmd.getColumnName(i).replaceAll(String.format("%s.",tableInfo.getTableName()),"");
                    for (String c:columnList
                    ) {
                        if(c.equalsIgnoreCase(titleName)){
                            object.put(c,result.getObject(i));
                            break;
                        }
                    }

                }
                data.add(object);
            }
        } catch(SQLException e) {
            log.error(e.getMessage());
        } finally {
            close();
        }
        return data;
    }

    public  Map<String,Object> dataPreview( Map<String, Object> params) {
        String tableName =params.get("tableName").toString();
        List<String> columnList = new ArrayList<>();
        List<JSONObject> data=new ArrayList<>();
        Map<String,Object> dataMap = new HashMap<>();
        try{
            String sql = String.format("select * from  %s",tableName);
            //导出不用分页
            if(null!=params.get("limit")&& null!=params.get("page") ){
                Integer pageSize = Integer.parseInt(params.get("limit").toString());
                Integer currPage =Integer.parseInt(params.get("page").toString());
                Integer start = (currPage-1)*pageSize;
                sql = sql + String.format(" limit %d, %d",start,pageSize);
            }
//            HiveQueryResultSet result = (HiveQueryResultSet) getResultSet(sql);
            ResultSet result = getResultSet(sql);
            ResultSetMetaData tsmd=result.getMetaData();
            for (int i = 1; i < tsmd.getColumnCount()+1; i++) {
                columnList.add(tsmd.getColumnName(i).replaceAll(String.format("%s.",tableName),""));
            }
            dataMap.put("columnList",columnList);
            while(result.next()){
                JSONObject object=new JSONObject();
                for (int i = 1; i < tsmd.getColumnCount()+1; i++) {
                    object.put(tsmd.getColumnName(i).replaceAll(String.format("%s.",tableName),""),result.getObject(i));
                }
                data.add(object);
            }
            dataMap.put("data",data);
        } catch(SQLException e) {
            log.error(e.getMessage());
        } finally {
            close();
        }
        return dataMap;
    }



    public ResultSet getResultSet(String sql) throws SQLException {
        PreparedStatement sta = con.prepareStatement(sql);
        return sta.executeQuery();
    }

    public void execute(String script) throws SQLException {
        Statement sta = con.createStatement();
        sta.execute(script);
    }

    public void close() {
        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
        }
    }
}
